import pymysql
from datetime import datetime, date, timedelta

yesterday = str((date.today() + timedelta(days=-1)).strftime("%Y-%m-%d"))
print(yesterday)


def load_data():
    # 连接数据库
    conn = pymysql.Connect(
        host='bcyang.rwlb.rds.aliyuncs.com',
        port=3306,
        user='buchou',
        passwd='Money@181108#',
        db='hog_prd',
        charset='utf8'
    )
    # conn = pymysql.Connect(
    #     host='118.190.162.235',
    #     port=3306,
    #     user='root',
    #     passwd='root',
    #     db='iot_user_db',
    #     charset='utf8'
    # )
    # 获取游标
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    #
    # print(cursor.execute('''show databases;'''))
    # print(cursor.fetchall())
    # print(cursor.execute('''show tables;'''))
    # print(cursor.fetchall())

    cursor.execute(('''
    select t.pitem001,t.pitem003,t.warnTime,t.num from 
    (
    SELECT
     a.pitem001,
     a.pitem003,
     date_format(a.alarmTime, '%Y-%m-%d') as warnTime,
     count(a.id) as num
    FROM
     bcw_cam_alarm a 
    WHERE
     a.aitype = 2
    AND a.alarmTime > date_sub(curdate(),interval 1 day) 
    and a.alarmTime < date_sub(curdate(),interval 0 day) 
    GROUP BY
     a.pitem001,
     date_format(a.alarmTime, '%Y-%m-%d')
    ) t
    order by t.num desc'''))
    all_devices = cursor.fetchall()#获取所有摄像头数据

    cursor.execute(('''
        select t.pitem001,t.pitem003,t.warnTime,t.num from 
    (
    SELECT
     a.pitem001,
     a.pitem003,
     date_format(a.alarmTime, '%Y-%m-%d') as warnTime,
     count(a.id) as num
    FROM
     bcw_cam_alarm a 
    WHERE
     a.aitype = 2     and a.g001 in ("10020", "10050", "10134", "10135", "10136", "10137", "10138", "10139", "10140", "10153", "10161", "10172", "10173","10141")
    AND a.alarmTime > date_sub(curdate(),interval 1 day) 
    and a.alarmTime < date_sub(curdate(),interval 0 day) 
    GROUP BY
     a.pitem001,
     date_format(a.alarmTime, '%Y-%m-%d')
    ) t
    order by t.num desc'''))
    daxiang_devices = cursor.fetchall()#只获取大象的数据
    abnormol_devices_string = ""
    all_devices_string = ""
    #拼接字符串用于发送
    for device in daxiang_devices:
        all_devices_string += device['pitem001'] + "  " \
                       + device['pitem003'] + "    " \
                       + device["warnTime"] + "    " \
                       + str(device['num']) + "   " \
                       + '\n'
    for device in all_devices:
        abnormol_devices_string += device['pitem001'] + "     " \
                            + device['pitem003'] + "     " \
                            + device["warnTime"] + "     " \
                            + str(device['num']) \
                            + '\n'
    print(abnormol_devices_string)
    print(all_devices_string)
    return abnormol_devices_string, all_devices_string


import smtplib
from email.mime.text import MIMEText
from email.utils import formataddr
from datetime import datetime, date, timedelta


# 传入邮件内容和邮件标题
def abnormal_mail(email_content, subject):
    abnormal_data_receivers = ['83942052@qq.com', 'xulong2016@163.com', '383870587@qq.com','lijianyu741@163.com']
    for my_user in abnormal_data_receivers:
        my_sender = '83942052@qq.com'  # 发件人邮箱账号
        my_pass = 'benzlgfffdzxbgde'  # 发件人邮箱密码

        ret = True
        try:
            msg = MIMEText(email_content, 'plain', 'utf-8')
            msg['From'] = formataddr(["不愁网每日提醒", my_sender])  # 括号里的对应发件人邮箱昵称、发件人邮箱账号
            msg['To'] = formataddr(["FK", my_user])  # 括号里的对应收件人邮箱昵称、收件人邮箱账号
            msg['Subject'] = subject  # 邮件的主题，也可以说是标题

            server = smtplib.SMTP_SSL("smtp.qq.com", 465)  # 发件人邮箱中的SMTP服务器，端口是25
            server.login(my_sender, my_pass)  # 括号中对应的是发件人邮箱账号、邮箱密码
            server.sendmail(my_sender, [my_user, ], msg.as_string())  # 括号中对应的是发件人邮箱账号、收件人邮箱账号、发送邮件
            server.quit()  # 关闭连接
        except Exception:  # 如果 try 中的语句没有执行，则会执行下面的 ret=False
            ret = False
    return ret


def all_data_mail(email_content, subject):
    all_data_receivers = ['1464828834@qq.com']  # 收件人邮箱账号
    # all_data_receivers = ['lijianyu741@163.com']  # 收件人邮箱账号
    for my_user in all_data_receivers:
        my_sender = '83942052@qq.com'  # 发件人邮箱账号
        my_pass = 'benzlgfffdzxbgde'  # 发件人邮箱密码

        ret = True
        try:
            msg = MIMEText(email_content, 'plain', 'utf-8')
            msg['From'] = formataddr(["不愁网每日提醒", my_sender])  # 括号里的对应发件人邮箱昵称、发件人邮箱账号
            msg['To'] = formataddr(["FK", my_user])  # 括号里的对应收件人邮箱昵称、收件人邮箱账号
            msg['Subject'] = subject  # 邮件的主题，也可以说是标题

            server = smtplib.SMTP_SSL("smtp.qq.com", 465)  # 发件人邮箱中的SMTP服务器，端口是25
            server.login(my_sender, my_pass)  # 括号中对应的是发件人邮箱账号、邮箱密码
            server.sendmail(my_sender, [my_user, ], msg.as_string())  # 括号中对应的是发件人邮箱账号、收件人邮箱账号、发送邮件
            server.quit()  # 关闭连接
        except Exception:  # 如果 try 中的语句没有执行，则会执行下面的 ret=False
            ret = False
    return ret


abnormal_data, all_data = load_data()
print(all_data)
if (abnormal_data == "[]"):
    abnormal_data = "今天无异常"
abnormal_mail(abnormal_data, "摄像头报警" + (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d"))
all_data_mail(all_data, "山西大象摄像头报警" + (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d"))
